IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchProperties]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SearchProperties]

GO

CREATE PROCEDURE [dbo].[SearchProperties]
(
@prmDistrictID  int  = null ,
@prmLocation  varchar(50) = null ,
@prmPropertyType  int  = null ,
@prmPurposeType  int  = null ,
@prmPriceMin  int  = null,
@prmPriceMax  int  = null,
@PageNumber as int = 1,                    
@PageSize as int = 0,                    
@TotalPages as int OUTPUT,                    
@TotalItems as int OUTPUT,                    
@FirstItem as int OUTPUT,                    
@LastItem as int OUTPUT   
)
AS
SET NOCOUNT ON
BEGIN

--Select * from PropertyDesc

CREATE TABLE #TempTable    
	(    
		ID int IDENTITY PRIMARY KEY,  
		PropertyID Uniqueidentifier,  
		Location [varchar](50) NULL,
		Address [varchar](500) NULL,
		PropertyDescription [varchar](1000) NULL,
		Name [varchar](100) NULL,
		EmailID [varchar](100) NULL,
		ContactNo [varchar](50) NULL,
		PreferredTime [varchar](200) NULL,
		Price [int] NULL,
	 )    
    
    Insert into #TempTable(PropertyID,Location, Address,PropertyDescription,Name,EmailID,ContactNo,PreferredTime,Price) 
	SELECT ID,Location, Address,PropertyDescription,Name,EmailID,ContactNo,PreferredTime,Price
	FROM PropertyDesc Where DistrictID = @prmDistrictID and PropertyType = @prmPropertyType
	and PurposeType = @prmPurposeType and ISNULL(Price,0) >= ISNULL(@prmPriceMin,0)
	and ISNULL(Price,1000000000) <= ISNULL(@prmPriceMax,1000000000)
	and (Location like '%'+@prmLocation + '%' OR Address like '%'+@prmLocation + '%' OR
	PropertyDescription like '%'+@prmLocation + '%' )
	Order By ModifiedOn Desc
	
	Select @TotalItems = Count(ID) from #TempTable    
	if @PageSize = 0    
	set @TotalPages = 1    
	else    
	Begin    
	Set @TotalPages = ceiling(cast(@TotalItems as decimal)/@PageSize)    
	End   

	if (@PageNumber > @TotalPages)    
	set @PageNumber = @TotalPages    
	else if @PageNumber < 1    
	set @PageNumber = 1   

	Declare @FirstRec as int    
	Declare @LastRec as int    
	Select @FirstRec = (@PageNumber - 1) * @PageSize    
	Select @LastRec = (@PageNumber * @PageSize + 1)    
	Select * from #TempTable where ID > @FirstRec and ID < @LastRec    
	Set @LastItem = @FirstRec+@@ROWCOUNT    
	Set @FirstItem = @FirstRec+1    
	drop table  #TempTable   


END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getProperty]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getProperty]

GO

CREATE PROCEDURE [dbo].[getProperty]
(
@prmID uniqueidentifier = null
)
AS
SET NOCOUNT ON
BEGIN

Select * from PropertyDesc Where ID = @prmID

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[saveProperty]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[saveProperty]

GO

CREATE PROCEDURE [dbo].[saveProperty]
(
@prmID uniqueidentifier = null,
@prmUserID uniqueidentifier = null,
@prmDistrictID  int  = null ,
@prmLocation  varchar(50) = null ,
@prmAddress  varchar(500) = null ,
@prmPropertyDesc  varchar(1000) = null ,
@prmName  varchar(100) = null ,
@prmEmailId  varchar(100) = null ,
@prmContactNo  varchar(50) = null ,
@prmPreferredTime  varchar(200) = null ,
@prmPropertyType  int  = null ,
@prmPurposeType  int  = null ,
@prmPrice  int  = null 
)
AS
SET NOCOUNT ON
BEGIN

IF @prmID IS NULL
BEGIN

	 INSERT INTO PropertyDesc (ID,DistrictID,Location,Address,PropertyDescription,Name,ContactNo,PreferredTime,
	 PropertyType,PurposeType,Price,Inactive,ModifiedOn,UserID,EmailID) 
	 VALUES(NEWID(),@prmDistrictID,@prmLocation,@prmAddress,@prmPropertyDesc,@prmName,@prmContactNo,@prmPreferredTime,
	 @prmPropertyType,@prmPurposeType,@prmPrice,0,GETDATE(),@prmUserID,@prmEmailId)

END
ELSE
BEGIN

	UPDATE PropertyDesc SET
	DistrictID = @prmDistrictID,
	Location = @prmLocation,
	Address = @prmAddress,
	PropertyDescription = @prmPropertyDesc,
	Name = @prmName,
	ContactNo = @prmContactNo,
	PreferredTime = @prmPreferredTime,
	PropertyType = @prmPropertyType,
	PurposeType = @prmPurposeType,
	Price = @prmPrice,
	EmailID = @prmEmailId,
	ModifiedOn = GETDATE()
	WHERE ID = @prmID

END
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getStates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getStates]

GO

CREATE PROCEDURE [dbo].[getStates]

AS
SET NOCOUNT ON
BEGIN

Select StateID,StateName from State

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDistricts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getDistricts]

GO

CREATE PROCEDURE [dbo].[getDistricts]
(
@prmStateID as int = null
)
AS
SET NOCOUNT ON
BEGIN

Select DistrictID,DistrictName from District where StateID = @prmStateID

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[createUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[createUser]

GO

CREATE PROCEDURE [dbo].[createUser]
(
@prmID uniqueidentifier = null ,
@prmPassword  varchar(100) = null ,
@prmEmailID  varchar(100) = null ,
@prmName  varchar(100) = null ,
@prmContactNo  varchar(50) = null ,
@prmError  int = null output
)
AS
SET NOCOUNT ON
BEGIN

SET @prmError = 0


IF NOT EXISTS (Select 1 from UserInfo Where ID = @prmID)
BEGIN

IF EXISTS(SELECT 1 from UserInfo Where EmailID = @prmEmailID)
BEGIN
	Set @prmError = 11
	RETURN
END
	
 INSERT INTO UserInfo (ID,EmailID,Password,Name,ContactNo,ModifiedOn,Inactive) 
 VALUES(NEWID(),@prmEmailID,@prmPassword,@prmName,@prmContactNo,GETDATE(),0)
END
ELSE
BEGIN
 UPDATE UserInfo SET 
Password = @prmPassword,
Name = @prmName,
ContactNo = @prmContactNo,
ModifiedOn = GETDATE() Where ID = @prmID
END

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[validateLogin]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[validateLogin]

GO

CREATE PROCEDURE [dbo].[validateLogin]
(
@prmPassword  varchar(100) = null ,
@prmEmailID  varchar(100) = null ,
@prmError  int = null output
)
AS
SET NOCOUNT ON
BEGIN

SET @prmError = -1

IF EXISTS(Select 1 from UserInfo Where EmailID = @prmEmailID and Inactive = 1)
BEGIN
SET @prmError = 1
RETURN
END

IF EXISTS(Select 1 from UserInfo Where EmailID = @prmEmailID and Password <> @prmPassword)
BEGIN
SET @prmError = 2
RETURN
END

IF EXISTS(Select 1 from UserInfo Where EmailID = @prmEmailID and Password = @prmPassword)
BEGIN
SET @prmError = 0
Select * from UserInfo Where EmailID = @prmEmailID and Password = @prmPassword
RETURN
END

IF NOT EXISTS(Select 1 from UserInfo Where EmailID = @prmEmailID)
BEGIN
SET @prmError = 3
RETURN
END



END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getUserProperties]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getUserProperties]

GO

CREATE PROCEDURE [dbo].[getUserProperties]
(
@prmUserID  Uniqueidentifier  = null ,
@prmLocation  varchar(50) = null ,
@prmStatus bit  = null,
@PageNumber as int = 1,                    
@PageSize as int = 0,                    
@TotalPages as int OUTPUT,                    
@TotalItems as int OUTPUT,                    
@FirstItem as int OUTPUT,                    
@LastItem as int OUTPUT   
)
AS
SET NOCOUNT ON
BEGIN

--Select * from PropertyDesc

CREATE TABLE #TempTable    
	(    
		ID int IDENTITY PRIMARY KEY,  
		PropertyID Uniqueidentifier,  
		PropertyType int, 
		PurposeType int,  
		Location [varchar](50) NULL,
		[Address] [varchar](500) NULL,
		PropertyDescription [varchar](1000) NULL,
		Name [varchar](100) NULL,
		EmailID [varchar](100) NULL,
		ContactNo [varchar](50) NULL,
		PreferredTime [varchar](200) NULL,
		Price [int] NULL,
	 )    
    
    Insert into #TempTable(PropertyID,PropertyType,PurposeType,Location, [Address],PropertyDescription,Name,EmailID,ContactNo,PreferredTime,Price) 
	SELECT ID,PropertyType,PurposeType,Location, [Address],PropertyDescription,Name,EmailID,ContactNo,PreferredTime,Price
	FROM PropertyDesc Where UserID = @prmUserID and @prmStatus = InActive and (Location like '%'+ISNULL(@prmLocation,'') + '%' OR Address like '%'+ISNULL(@prmLocation,'') + '%' OR
	PropertyDescription like '%'+ISNULL(@prmLocation,'') + '%' )
	Order By ModifiedOn Desc
	
	Select @TotalItems = Count(ID) from #TempTable    
	if @PageSize = 0    
	set @TotalPages = 1    
	else    
	Begin    
	Set @TotalPages = ceiling(cast(@TotalItems as decimal)/@PageSize)    
	End   

	if (@PageNumber > @TotalPages)    
	set @PageNumber = @TotalPages    
	else if @PageNumber < 1    
	set @PageNumber = 1   

	Declare @FirstRec as int    
	Declare @LastRec as int    
	Select @FirstRec = (@PageNumber - 1) * @PageSize    
	Select @LastRec = (@PageNumber * @PageSize + 1)    
	Select * from #TempTable where ID > @FirstRec and ID < @LastRec    
	Set @LastItem = @FirstRec+@@ROWCOUNT    
	Set @FirstItem = @FirstRec+1    
	drop table  #TempTable   


END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[changePropertyStatus]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[changePropertyStatus]

GO

CREATE PROCEDURE [dbo].[changePropertyStatus]
(
@prmID  UNIQUEIDENTIFIER ,
@prmStatus  bit 
)
AS
SET NOCOUNT ON
BEGIN

Update PropertyDesc
Set Inactive = @prmStatus,ModifiedOn = GETDATE()
Where ID = @prmID

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getPropertyByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getPropertyByID]

GO

CREATE PROCEDURE [dbo].[getPropertyByID]
(
@prmID  UNIQUEIDENTIFIER
)
AS
SET NOCOUNT ON
BEGIN

Select pd.*,d.StateID
from PropertyDesc pd left join District d On pd.DistrictID = d.DistrictID
Where ID = @prmID

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDistrictLocations]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getDistrictLocations]

GO

CREATE PROCEDURE [dbo].[getDistrictLocations]
(
@prmDistrictID  int ,
@prmName varchar(50)
)
AS
SET NOCOUNT ON
BEGIN

Select Name
from Location
Where DistrictID = @prmDistrictID and Name like @prmName + '%'

END
GO